import pymysql

# 连接MySQL数据库
try:
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='zhang77is',
        db='当当网',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )

    if connection.open:
        print("Connected to the database")
        cursor = connection.cursor()

        # 创建表（如果不存在）
        create_table_query = """
        CREATE TABLE IF NOT EXISTS news (
            id INT AUTO_INCREMENT PRIMARY KEY,
            href VARCHAR(255),
            shownewstitle VARCHAR(255),
            shownewdate DATE,
            shownewscontent TEXT
        );
        """
        cursor.execute(create_table_query)

        # 插入数据到MySQL
        insert_query = """
        INSERT INTO news (href, shownewstitle, shownewdate, shownewscontent)
        VALUES (%s, %s, %s, %s);
        """
        data = ('http://example.com', 'Example Title', '2023-10-01', 'Example content')
        cursor.execute(insert_query, data)
        connection.commit()

except pymysql.MySQLError as e:
    print(f"Error connecting to MySQL: {e}")

finally:
    if connection.open:
        cursor.close()
        connection.close()
        print("MySQL connection is closed")